﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using net.xBei.Helper;
using MySqlConnector;

namespace net.xBei.Models.FilterCondition {
    /// <summary>
    /// 
    /// </summary>
    public interface ICondition {
        /// <summary>
        /// 搜索条件名称
        /// </summary>
        string Name { get; }
        /// <summary>
        /// 描述
        /// </summary>
        string Description { get; }
        /// <summary>
        /// 要搜索的字段
        /// </summary>
        string FieldName { get; }
        /// <summary>
        /// 是否需要连接表
        /// </summary>
        bool MustJoin { get; }
        /// <summary>
        /// 链接的表名
        /// </summary>
        string JoinTable { get; }
        /// <summary>
        /// 获取连接表的Sql
        /// </summary>
        /// <returns></returns>
        string GetJoin();
    }
    public class Factory {
        private static readonly List<ICondition> conditions = new List<ICondition>();
        public static void Add(ICondition item) {
            conditions.Add(item);
        }
        public static IEnumerable<object> GetConditions() {
            return conditions.Select(c => new {
                c.Name,
                c.Description
            });
        }
        public static (List<string> JoinTableList, string Where, IEnumerable<MySqlParameter> parameters) BuilderSql(ConditionItem filter, IEnumerable<string> existsJoinTableList) {
            var joinTables = new List<string>();
            var parameters = new List<MySqlParameter>();
            var op = filter.Range == ConditionRange.All ? " AND " : " OR ";
            var where = filter.Conditions.Select(c => Parse(c, existsJoinTableList, joinTables, parameters)).Where(s => string.IsNullOrWhiteSpace(s) == false).JoinBy(op);
            return (joinTables, where, parameters);
        }
        internal static string GetParameterName(IEnumerable<MySqlParameter> parameters) {
            return $"p{parameters.Count() + 1}";
        }
        internal static string Parse(ConditionItem item, IEnumerable<string> existsJoinTableList, List<string> joinTables, List<MySqlParameter> parameters) {
            if (item.Conditions?.Count > 0) {
                var op = item.Range == ConditionRange.All ? " AND " : " OR ";
                var where = item.Conditions.Select(c => Parse(c, existsJoinTableList, joinTables, parameters)).Where(s => string.IsNullOrWhiteSpace(s) == false).JoinBy(op);
                return $"({where})";
            }
            var cc = conditions.FirstOrDefault(c => c.Name == item.Field);
            if (cc == null) return string.Empty;
            if (cc.MustJoin && existsJoinTableList.Contains(cc.JoinTable) == false) {
                joinTables.Add(cc.GetJoin());
            }
            var name = GetParameterName(parameters);
            switch (item.Operator) {
                case "=":
                case ">":
                case "<":
                case ">=":
                case "<=":
                case "!=": {
                        //TODO::
                        //if (item.Result is Newtonsoft.Json.Linq.JArray jArray && jArray.HasValues) {
                        //    parameters.Add(new MySqlParameter(name, jArray.Last.ToObject()));
                        //    return $"({cc.FieldName} {item.Operator} @{name})";
                        //}
                        parameters.Add(new MySqlParameter(name, item.Result));
                        return $"({cc.FieldName} {item.Operator} @{name})";
                    }
                case "^=":
                    parameters.Add(new MySqlParameter(name, $"{item.Result}%"));
                    return $"({cc.FieldName} LIKE @{name})";
                case "!^=":
                    parameters.Add(new MySqlParameter(name, $"{item.Result}%"));
                    return $"({cc.FieldName} NOT LIKE @{name})";
                case "()":
                    parameters.Add(new MySqlParameter(name, $"%{item.Result}%"));
                    return $"({cc.FieldName} LIKE @{name})";
                case "!()":
                    parameters.Add(new MySqlParameter(name, $"%{item.Result}%"));
                    return $"({cc.FieldName} NOT LIKE @{name})";
                case "{}": {
                        //if (item.Result is Newtonsoft.Json.Linq.JArray jArray && jArray.HasValues) {
                        //    var list = GetArray(jArray, parameters);
                        //    return $"{cc.FieldName} IN ({list.Select(p => $"@{p.ParameterName}").JoinBy(",")})";
                        //} 
                        if (item.Result is string jString) {
                            var list = GetArray(jString, parameters);
                            return $"{cc.FieldName} IN ({list.Select(p => $"@{p.ParameterName}").JoinBy(",")})";
                        }
                        return "";
                    }
                case "!{}": {
                        //if (item.Result is Newtonsoft.Json.Linq.JArray jArray && jArray.HasValues) {
                        //    var list = GetArray(jArray, parameters);
                        //    return $"{cc.FieldName} NOT IN ({list.Select(p => $"@{p.ParameterName}").JoinBy(",")})";
                        //} 
                        if (item.Result is string jString) {
                            var list = GetArray((string)item.Result, parameters);
                            return $"{cc.FieldName} NOT IN ({list.Select(p => $"@{p.ParameterName}").JoinBy(",")})";
                        }
                        return "";
                    }
                default:
                    return "";
            }
        }
        public static IEnumerable<MySqlParameter> GetArray(string str, List<MySqlParameter> parameters) {
            var list = FixArrayResult(str);
            var ps = new List<MySqlParameter>();
            foreach (var v in list) {
                var p = new MySqlParameter {
                    ParameterName = GetParameterName(parameters),
                    Value = v,
                    DbType = System.Data.DbType.String,
                };
                ps.Add(p);
                parameters.Add(p);
            }
            return ps;
        }
        //public static IEnumerable<MySqlParameter> GetArray(Newtonsoft.Json.Linq.JArray list, List<MySqlParameter> parameters) {
        //    var ps = new List<MySqlParameter>();
        //    foreach (var v in list) {
        //        var p = new MySqlParameter {
        //            ParameterName = GetParameterName(parameters),
        //            Value = v.ToObject(),
        //        };
        //        ps.Add(p);
        //        parameters.Add(p);
        //    }
        //    return ps;
        //}
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IEnumerable<MySqlParameter> GetArray<T>(IEnumerable<T> list, List<MySqlParameter> parameters) {
            var ps = new List<MySqlParameter>();
            foreach (var v in list) {
                if (v is not MySqlParameter p) {
                    p = new MySqlParameter {
                        ParameterName = GetParameterName(parameters),
                        Value = v,
                    };
                }
                ps.Add(p);
                parameters.Add(p);
            }
            return ps;
        }
        public static MySqlParameter AddParameter(string value, List<MySqlParameter> parameters) {
            var p = new MySqlParameter($"KEY{parameters.Count + 1}", value);
            parameters.Add(p);
            return p;
        }
        public static MySqlParameter AddParameter(int value, List<MySqlParameter> parameters) {
            var p = new MySqlParameter($"KEY{parameters.Count + 1}", value);
            parameters.Add(p);
            return p;
        }
        public static MySqlParameter AddParameter(decimal value, List<MySqlParameter> parameters) {
            var p = new MySqlParameter($"KEY{parameters.Count + 1}", value);
            parameters.Add(p);
            return p;
        }
        public static MySqlParameter AddParameter(DateTime value, List<MySqlParameter> parameters) {
            var p = new MySqlParameter($"KEY{parameters.Count + 1}", value);
            parameters.Add(p);
            return p;
        }
        static Regex regFunc = new Regex("^\\s*([a-z_]+)\\s*?\\((.+)\\s*\\)\\s*$", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        public static string FixSqlKeyName(string input)
            => input == "''"
                ? input
                : (input.Contains('`') ? input.Trim() : $"`{input.Trim()}`");
        public static string FixSqlField(string input) {
            var m = regFunc.Match(input);
            if (m.Success) {
                return $"{m.Groups[1].Value}({FixSqlField(m.Groups[2].Value)})";
            } else if (input.Contains(',')) {
                //var partList = input.Split(",");
                //暂时先这样
                return input;
            } else {
                return decimal.TryParse(input, out var _)
                ? input.Trim()
                : (input.Contains('.')
                    ? input.Split('.').Select(FixSqlKeyName).JoinBy('.')
                    : FixSqlKeyName(input));
            }
        }
        public static string FixSelectField(string input) {
            input = input.Trim();
            if (input == "*") {
                return input;
            }
            if (input.Contains('(') && input.Contains(')')) {
                return input;
            }
            if (input.Contains(' ') == false) {
                return FixSqlField(input);
            }
            var l1 = (new Regex("\\s+AS\\s+", RegexOptions.Compiled | RegexOptions.IgnoreCase)).Split(input);
            if (l1.Length <= 1) l1 = input.Split(' ');
            if (l1.Length <= 1) return FixSqlField(l1.FirstOrDefault() ?? string.Empty);
            return $"{FixSqlField(l1[0])} AS '{l1[1]}'";
        }
        internal static IEnumerable<string> FixArrayResult(string str) {
            // $result = str_replace('，',',',$result);
            // $result = str_replace('、', ',',$result);
            // $result = str_replace(':', '',$result);
            return str.Replace('，', ',')
                .Replace('、', ',')
                .Replace(":", "")
                .Split(",")
                .Where(s => string.IsNullOrWhiteSpace(s) == false);
        }
    }
}
